我需要知道表格中的行数来计算百分比。如果总计数大于某个预定义的常量,我将使用常量值。否则,我将使用实际的行数。
我可以使用SELECT count(*) FROM table. 但是如果我的常量值为 500,000 并且我的表中有 5,000,000,000 行,那么计算所有行将浪费大量时间。
SELECT count(*) FROM table
一旦超过我的恒定值,是否可以停止计数?
只要它低于给定的限制,我就需要确切的行数。否则,如果计数超过限制,我将使用限制值并希望尽快得到答案。
像这样的东西:
SELECT text,count(*), percentual_calculus() FROM token GROUP BY text ORDER BY count DESC;
众所周知,在 PostgreSQL 中计算大表中的行数很慢。MVCC模型需要完整的活动行数才能获得精确的数字。如果计数不必 与* 您的情况 完全相同 ,则有一些解决方法可以 显着加快这一速度。 ***
(请记住,在并发写入负载下,即使是“精确”计数也可能在到达时失效。)
大桌子慢 。 使用并发写操作,它可能在你得到它的那一刻就已经过时了。
SELECT count(*) AS exact_count FROM myschema.mytable;
极快: _
SELECT reltuples AS estimate FROM pg_class where relname = 'mytable';
通常,估计值非常接近。多接近,取决于是否ANALYZE运行VACUUM足够 - 其中“足够”由表的写入活动级别定义。
ANALYZE
VACUUM
以上忽略了在一个数据库中具有相同名称的多个表的可能性 - 在不同的模式中。为了解决这个问题:
SELECT c.reltuples::bigint AS estimate FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 'mytable' AND n.nspname = 'myschema';
演员可以很好地bigint格式化real数字,特别是对于大计数。
bigint
real
SELECT reltuples::bigint AS estimate FROM pg_class WHERE oid = 'myschema.mytable'::regclass;
更快、更简单、更安全、更优雅。请参阅有关对象标识符类型的手册。
在 Postgres 9.4+ 中替换'myschema.mytable'::regclass为to_regclass('myschema.mytable')不获取任何内容,而不是获取无效表名的异常。看:
'myschema.mytable'::regclass
to_regclass('myschema.mytable')
我们可以做 Postgres 规划器所做的事情。引用手册中的 _ 行估计示例_ :
这些数字是最新的VACUUM或ANALYZE在桌子上的。然后,规划器获取表中实际的当前页数(这是一个廉价的操作,不需要表扫描)。relpages如果这与then不同,则reltuples相应地缩放以得出当前的行数估计。
relpages
reltuples
Postgres 使用estimate_rel_size定义的 in src/backend/utils/adt/plancat.c,它还涵盖了没有数据 in 的极端情况,pg_class因为关系从未被清理过。我们可以在 SQL 中做类似的事情:
estimate_rel_size
src/backend/utils/adt/plancat.c
pg_class
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint FROM pg_class WHERE oid = 'mytable'::regclass; -- your table here
SELECT (CASE WHEN c.reltuples < 0 THEN NULL -- never vacuumed WHEN c.relpages = 0 THEN float8 '0' -- empty table ELSE c.reltuples / c.relpages END * (pg_catalog.pg_relation_size(c.oid) / pg_catalog.current_setting('block_size')::int) )::bigint FROM pg_catalog.pg_class c WHERE c.oid = 'myschema.mytable'::regclass; -- schema-qualified table here
不会与空表和从未见过的表中断VACUUM或ANALYZE。手册pg_class:
如果该表尚未被清理或分析,则reltuples包含-1指示行数未知。
-1
如果此查询返回,则为表NULL运行ANALYZEor并重复。VACUUM(或者,您可以像 Postgres 那样根据列类型估计行宽,但这很乏味且容易出错。)
NULL
如果此查询返回0,则该表似乎是空的。但我会ANALYZE确定。(也许检查你的autovacuum设置。)
0
autovacuum
通常block_size是 8192。current_setting('block_size')::int涵盖罕见的例外情况。
block_size
current_setting('block_size')::int
表和模式限定使其不受任何search_path和范围的影响。
search_path
无论哪种方式,查询对我来说始终花费 < 0.1 ms。
更多网络资源:
TABLESAMPLE SYSTEM (n)
select.html#SQL-FROM)在 Postgres 9.5+
SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);
就像@a_horse 评论的那样,如果由于某种原因统计信息不够最新,则为命令添加的子句SELECT可能很有用。pg_class例如:
SELECT
INSERT
UPDATE
DELETE
TEMPORARY
这仅查看随机 n % (1在示例中)选择的块并计算其中的行数。更大的样本会增加成本并减少错误,您的选择。准确性取决于更多因素:
1
FILLFACTOR
通常,来自的估计pg_class会更快、更准确。
首先,我需要知道该表中的行数,如果总计数大于某个预定义的常量,
以及是否…
…可能在计数通过我的常量值的那一刻,它将停止计数(而不是等待完成计数以通知行数更大)。
是的。 您可以使用 子查询LIMIT:
LIMIT
SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;
Postgres 实际上停止 超出给定限制的计数,您可以获得最多 n 行(在示例中为 500000)的 准确和当前 计数,否则为 n 。 不过,它的速度几乎没有 中的估计值那么快。 pg_class